/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package cn.topcodes.tcsf.codegen.utils;

import cn.topcodes.tcsf.codegen.domain.entity.Field;
import com.mysql.jdbc.StringUtils;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author 大爱阳哥
 */
public class JdbcUtil {
    
    private static final String MYSQL_URL = "jdbc:mysql://%s:%s%s?user=%s&password=%s&useUnicode=true&characterEncoding=utf-8";
    
    private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
    
    private static final String ORACLE_URL = "";
    
    private static final String ORACLE_DRIVER = "";
    
    private static final String SQLSERVER_URL = "";
    
    private static final String SQLSERVER_DRIVER = "";
    
    private static Connection getConnection(String type,String ip, int port, String username,
            String password, String dbName) {
        String url = null;
        String driver = null;
        switch(type) {
            case "MySQL":
                url = String.format(MYSQL_URL, ip,port,dbName,username,password);
                driver = MYSQL_DRIVER;
                break;
            case "Oracle":
                url = ORACLE_URL;
                driver = ORACLE_DRIVER;
                break;
            case "SqlServer":
                url = SQLSERVER_URL;
                driver = SQLSERVER_DRIVER;
                break;
            default:
                return null;
        }
        try {
            Class.forName(driver);
            return DriverManager.getConnection(url);
        } catch (Exception e) {
            System.err.println("error:数据库连接失败");
            e.printStackTrace();
        }
        return null;
    }
    
    private static void release(ResultSet rs, Statement stat, Connection conn) {
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                System.err.println("error:" + ex.getMessage());
                ex.printStackTrace();
            }
        }
        if(stat != null) {
            try {
                stat.close();
            } catch (SQLException ex) {
                System.err.println("error:" + ex.getMessage());
                ex.printStackTrace();
            }
        }
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                System.err.println("error:" + ex.getMessage());
                ex.printStackTrace();
            }
        }
    }
    
    private static String convertDataType(String typeName) {
        String dataType = null;
        switch(typeName) {
            case "BIGINT":
            case "NUMERIC":
                dataType = "Long";
                break;
            case "TINYINT":
            case "SMALLINT":
            case "INTEGER":
            case "INT":
            case "MEDIUMINT":
                dataType = "Integer";
                break;
            case "REAL":
            case "FLOAT":
                dataType = "Float";
                break;
            case "DOUBLE":
                dataType = "Double";
                break;
            case "VARCHAR":
            case "CHAR":
            case "LONGVARCHAR":
            case "TEXT":
                dataType = "String";
                break;
            case "DATE":
            case "DATETIME":
            case "TIMESTAMP":
                dataType = "Date";
                break;
            case "BIT":
                dataType = "Boolean";
                break;
            case "VARBINARY":
            case "BINARY":
            case "BLOB":
                dataType = "byte[]";
                break;
        }
        return dataType;
    }
    
    public static List<Field> getFields(String type,String ip, int port, String username,
            String password, String dbName, String tableName) {
        List<Field> fields = null;
       
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = getConnection(type,ip,port,username,password,"/" + dbName);
            DatabaseMetaData meta = conn.getMetaData();
            rs = meta.getColumns(dbName, null, tableName, null);
            fields = new ArrayList<Field>();
            while(rs.next()){
                Field field = new Field();
                String columnName = rs.getString("COLUMN_NAME");
                if("id".equals(columnName) || "created_time".equals(columnName) ||
                        "updated_time".equals(columnName) || "data_state".equals(columnName)) {
                    continue;
                }
                field.setFieldName(columnName);
                field.setPropertyName(NameUtil.underline2Camel(columnName));
                
                String remarks = rs.getString("REMARKS");
                if(StringUtils.isNullOrEmpty(remarks)) {
                    field.setDisplayName(NameUtil.underline2Camel(columnName));
                }else {
                    field.setDisplayName(remarks);
                }
                String typeName = rs.getString("TYPE_NAME");
                field.setDbDataType(typeName);
                String dataType = convertDataType(typeName);
                String inputType = convertInputType(dataType);
                field.setInputType(inputType);
                field.setJdbcDataType(dataType);
                field.setAllowNull(rs.getBoolean("NULLABLE"));
                field.setUnique(false);
                field.setDisplayField(true);
                field.setFormField(true);
                field.setRegExp("");
                field.setI18n("");
                field.setReadonly(false);
                field.setPlainPassword(false);
                field.setEncryptPassword(true);
                field.setConfirmPassword(true);
                field.setSubInputType(convertSubInputType(inputType));
                field.setSelectItems("Male=男,commons.gender,selected");
                field.setCurrentDate(false);
                field.setTimeFormat("yyyy-MM-dd HH:mm:ss");
                field.setTimeLimit("选择不限");
                field.setDefaultValue(getDefaultValue(dataType));
                field.setQueryField(false);
                field.setQueryCondition("EQ");
                field.setQueryDataType(getQueryDataType(field.getJdbcDataType()));
                
                try {
                    field.setMax(Double.parseDouble(rs.getString("COLUMN_SIZE")));
                }catch(Exception e) {
                    field.setMax(0.0);
                }
                field.setMin(0.0);
                fields.add(field);
            }
        }catch(Exception e) {
            System.err.println("error:数据库连接失败");
            e.printStackTrace();
        }finally {
             release(rs,null,conn);
        }
        return fields;        
    }
    
    public static List<String> getDatabases(String type, String ip, int port, String username, String password) {
        List<String> databases = null;
        
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = getConnection(type, ip, port, username, password,"");
            DatabaseMetaData meta = conn.getMetaData();
            rs = meta.getCatalogs();
            databases = new ArrayList<String>();
            while(rs.next()){
                databases.add(rs.getString("TABLE_CAT"));
            }
        }catch(Exception e) {
            System.err.println("error:数据库连接失败");
            e.printStackTrace();
        }finally {
             release(rs,null,conn);
        }
        return databases;        
    }

    public static String getTableRemark(String type, String ip, int port, String username, String password, String dbName,String table) { 
        String remark = null;
        
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection(type, ip, port, username, password,"/" + dbName);
            ps = conn.prepareStatement("select TABLE_COMMENT as REMARKS from INFORMATION_SCHEMA.TABLES where table_schema = ? and table_name = ?");
            ps.setString(1, dbName);
            ps.setString(2, table);
            rs = ps.executeQuery();
            while(rs.next()){
                remark = rs.getString("REMARKS");
                break;
            }
        }catch(Exception e) {
            System.err.println("error:数据库连接失败");
            e.printStackTrace();
        }finally {
             release(rs,ps,conn);
        }
        return remark;
    }
    
    public static List<String> getTables(String type, String ip, int port, String username, String password, String dbName) {
        List<String> tables = null;
        
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = getConnection(type, ip, port, username, password,"/" + dbName);
            DatabaseMetaData meta = conn.getMetaData();
            rs = meta.getTables(dbName, null, null, new String[] {"TABLE","VIEW"});
            tables = new ArrayList<String>();
            while(rs.next()){
                tables.add(rs.getString("TABLE_NAME"));
            }
        }catch(Exception e) {
            System.err.println("error:数据库连接失败");
            e.printStackTrace();
        }finally {
             release(rs,null,conn);
        }
        return tables;        
    }
    
    private static String convertInputType(String dataType) {
        switch(dataType) {
            case "Date":
                return "日期";
            case "Integer":
            case "Long":
            case "Float":
            case "Double":
                return "数值";
            case "String":
                return "文本";
            case "Boolean":
                return "选择";
        }
        return "文本";
    }
    
    private static String convertSubInputType(String inputType) {
        switch(inputType) {
            case "数值":
                return "整数";
            case "文本":
                return "普通文本";
            case "选择":
                return "下拉单选框";
        }
        return "";
    }
    
    private static String getDefaultValue(String dataType) {
        switch(dataType) {
            case "Date":
                return "2017-06-18 03:13:00";
            case "Integer":
            case "Long":
            case "Float":
            case "Double":
                return "0";
            case "String":
                return "";
            case "Boolean":
                return "False";
        }
        return "";
    }

    private static String getQueryDataType(String jdbcDataType) {
        switch(jdbcDataType) {
            case "Date":
                return "D";
            case "Integer":
                return "I";
            case "Long":
                return "L";
            case "Float":
            case "Double":
                return "N";
            case "String":
                return "S";
            case "Boolean":
                return "B";
        }
        return "";
    }
}
